package jdbc;

import java.lang.reflect.AccessibleObject;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;


import course.Course;

public class CourseDao extends DataAccessObject {
	private final String prefix = "CR_";
	private final String table = " Course ";
	private final Class<?> objectClass = Course.class;

	public synchronized ArrayList<Course> selectAllCourses() {

		ArrayList<Course> courses = null;
		Connection conn = null;
		CallableStatement callStat = null;
		try {
			conn = getConnection();
			callStat = conn.prepareCall("SELECT * FROM " + table + "ORDER BY "
					+ prefix + "ID");
			callStat.execute();
			ResultSet res = callStat.getResultSet();

			courses = new ArrayList<Course>();
			Field[] fields = objectClass.getDeclaredFields();
			AccessibleObject.setAccessible(fields, true);

			while (res.next()) {
				Course course = new Course();
				for (Field f : fields) {
					setFieldFromResultSet(res, course, prefix, f);
				}
				courses.add(course);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (SecurityException e) {
			e.printStackTrace();
		} catch (IllegalArgumentException e) {
			e.printStackTrace();
		} catch (NoSuchMethodException e) {
			e.printStackTrace();
		} catch (IllegalAccessException e) {
			e.printStackTrace();
		} catch (InvocationTargetException e) {
			e.printStackTrace();
		} finally {
			DataAccessObject.silentClose(conn);
		}
		return courses;
	}

	public synchronized void insertCourse(Course course) {
		Connection conn = null;
		try {
			if (course != null && course.getTeacherId() != null) {
				conn = getConnection();
				PreparedStatement prepStat = null;
				prepStat = conn
						.prepareStatement("INSERT INTO "
								+ table
								+ " (cr_id, cr_ownerId, cr_name, cr_price,"
								+ "cr_hours, cr_rating, cr_comment, cr_active,  "
								+ "cr_teacherId, cr_electiveId) VALUES (?,?,?,?,?,?,?,?,?,?)");

				prepStat.setLong(1, course.getId());
				prepStat.setLong(2, course.getOwnerId());
				prepStat.setString(3, course.getName());
				prepStat.setInt(4, course.getPrice());
				prepStat.setInt(5, course.getHours());
				prepStat.setDouble(6, course.getRating());
				prepStat.setString(7, course.getComment());
				prepStat.setBoolean(8, course.isActive());
				prepStat.setLong(9, course.getTeacherId());
				prepStat.setLong(10, course.getElectiveId());
				prepStat.execute();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DataAccessObject.silentClose(conn);
		}
	}

	public Course selectCourseById(Long id) {
		if (id == null) {
			System.out.println("NULL id in select " + table + "by id! " + table
					+ "not selected!");
			return null;
		}

		Course course = null;
		Connection conn = null;
		try {
			conn = getConnection();
			PreparedStatement prepStat = null;
			prepStat = conn.prepareStatement("SELECT * FROM " + table
					+ " WHERE (" + prefix + "ID) = (?);");

			prepStat.setLong(1, id.longValue());

			prepStat.execute();
			ResultSet res = prepStat.getResultSet();
			if (res.first()) {
				Field[] fields = objectClass.getDeclaredFields();
				AccessibleObject.setAccessible(fields, true);
				course = new Course();
				for (Field f : fields) {
					setFieldFromResultSet(res, course, prefix, f);
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (NoSuchMethodException e) {
			e.printStackTrace();
		} catch (IllegalAccessException e) {
			e.printStackTrace();
		} catch (InvocationTargetException e) {
			e.printStackTrace();
		} finally {
			DataAccessObject.silentClose(conn);
		}
		return course;
	}

	public void updateCourse(Course course) {
		Connection conn = null;
		try {
			conn = getConnection();
			PreparedStatement prepStat = null;
			prepStat = conn
					.prepareStatement("UPDATE "	+ table	+ "set"
							+ " cr_ownerId = ?, cr_name = ?, cr_price = ?,"
							+ " cr_hours = ?, cr_rating = ?, cr_comment = ?, cr_active = ?,  "
							+ " cr_teacherId = ?, cr_electiveId = ?"
							+ " where cr_id = ?");

			prepStat.setLong(1, course.getOwnerId());
			prepStat.setString(2, course.getName());
			prepStat.setInt(3, course.getPrice());
			prepStat.setInt(4, course.getHours());
			prepStat.setDouble(5, course.getRating());
			prepStat.setString(6, course.getComment());
			prepStat.setBoolean(7, course.isActive());
			prepStat.setLong(8, course.getTeacherId());
			prepStat.setLong(9, course.getElectiveId());
			prepStat.setLong(10, course.getId());
			prepStat.execute();
			
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DataAccessObject.silentClose(conn);
		}
	}
	
	public void deleteCourse(Course course) {
		Connection conn = null;
		try {
			conn = getConnection();
			PreparedStatement prepStat = null;
			if (course!=null && course.getId() != null) {
				prepStat = conn.prepareStatement("delete from " + table
						+ "where " + prefix + "ID = ?");
				prepStat.setLong(1, course.getId());
				prepStat.execute();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DataAccessObject.silentClose(conn);
		}
	}
}
